const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'mi1781173071',
  database: process.env.DB_NAME || 'dance_training',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 测试连接
pool.getConnection()
  .then(connection => {
    console.log('数据库连接成功');
    connection.release();
  })
  .catch(err => {
    console.error('数据库连接失败:', err);
  });

// 创建数据库操作工具类
class DB {
  // 执行SQL查询
  static async query(sql, params = []) {
    try {
      const [rows] = await pool.execute(sql, params);
      return rows;
    } catch (error) {
      console.error('SQL执行错误:', error);
      throw error;
    }
  }

  // 获取单条记录
  static async findOne(sql, params = []) {
    try {
      const rows = await this.query(sql, params);
      return rows[0] || null;
    } catch (error) {
      throw error;
    }
  }

  // 插入数据
  static async insert(table, data) {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const sql = `INSERT INTO ${table} (${keys.join(',')}) VALUES (${keys.map(() => '?').join(',')})`;
    try {
      const result = await this.query(sql, values);
      return result;
    } catch (error) {
      throw error;
    }
  }

  // 更新数据
  static async update(table, data, where) {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const whereKeys = Object.keys(where);
    const whereValues = Object.values(where);
    
    const sql = `UPDATE ${table} SET ${keys.map(key => `${key} = ?`).join(',')} WHERE ${whereKeys.map(key => `${key} = ?`).join(' AND ')}`;
    
    try {
      const result = await this.query(sql, [...values, ...whereValues]);
      return result;
    } catch (error) {
      throw error;
    }
  }

  // 删除数据
  static async delete(table, where) {
    const keys = Object.keys(where);
    const values = Object.values(where);
    const sql = `DELETE FROM ${table} WHERE ${keys.map(key => `${key} = ?`).join(' AND ')}`;
    
    try {
      const result = await this.query(sql, values);
      return result;
    } catch (error) {
      throw error;
    }
  }

  // 事务处理
  static async transaction(callback) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const result = await callback(connection);
      await connection.commit();
      return result;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
}

module.exports = DB; 